﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DomainObject.Administration.UserManagement;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{

    public partial class recCorporateActionRepo : ISearchable<recCorporateAction>
    {

        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);

                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo 
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo 
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo 
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo 
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recCorporateAction", column);
                            sbQuery.AppendFormat(@"         left join master.recDebtorSector on recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID
                                                            left join master.recSubSubSector on recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID
                                                            left join master.recDebtor on recDebtorSector.DebtorID = recDebtor.DebtorID
                                                            left join transactions.recBilling on recCorporateAction.BillingNo = recBilling.BillingNo 
                                                            left join master.recDebtor Debtor on recCorporateAction.DebtorID = Debtor.DebtorID {0}", parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recCorporateAction> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recCorporateAction> tt = new List<recCorporateAction>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat(@"         recCorporateAction.CorporateActionID 
                                                  , ISNULL(recSubSubSector.SubSubSectorName, recCorporateAction.CorporateSector) SubSubSectorName
                                                  , recCorporateAction.CorporateName
		                                          , Debtor.DebtorName        
	                                              , recCorporateAction.ImpositionValue
	                                              , recCorporateAction.InvoiceNo
                                                  , recCorporateAction.PaymentNo
                                                  , recBilling.Value");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     	transactions.recCorporateAction 
                                                    left join master.recDebtorSector on (recCorporateAction.DebtorSectorID = recDebtorSector.DebtorSectorID)
                                                    left join master.recSubSubSector on (recDebtorSector.SubSubSectorID = recSubSubSector.SubSubSectorID)
                                                    left join master.recDebtor on (recDebtorSector.DebtorID = recDebtor.DebtorID)
                                                    left join transactions.recBilling on (recCorporateAction.BillingNo = recBilling.BillingNo)
                                                    left join master.recDebtor Debtor on (recCorporateAction.DebtorID = Debtor.DebtorID)");
                    sbQuery.AppendFormat(" {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recCorporateAction t = new recCorporateAction(true);
                                t.CorporateActionID = dr[recCorporateAction.Fields.CorporateActionID] == DBNull.Value ? 0 : Convert.ToInt32(dr[recCorporateAction.Fields.CorporateActionID]);
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();

                                t.CorporateName = dr[recCorporateAction.Fields.CorporateName] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.CorporateName].ToString();
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();

                                t.ImpositionValue = dr[recCorporateAction.Fields.ImpositionValue] == DBNull.Value ? (decimal?)0 : Convert.ToDecimal(dr[recCorporateAction.Fields.ImpositionValue]);
                                t.InvoiceNo = dr[recCorporateAction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.InvoiceNo].ToString();
                                t.PaymentNo = dr[recCorporateAction.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.PaymentNo].ToString();
                                t.recBilling.Value = dr[recBilling.Fields.Value] == DBNull.Value ? 0m : Convert.ToDecimal(dr[recBilling.Fields.Value]);
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(recCorporateAction corporateAction, recInvoice invoice, recInvoiceDetail invoiceDetail, recBilling billing, string sectorCode, string subSectorCode)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {

                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = @"
                                begin
                                    declare @max int = 1;

                                    if Exists(SELECT ISNULL(MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5))), 0) InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()) AND SUBSTRING(InvoiceNo,1,1)=@SectorCode AND SUBSTRING(InvoiceNo,3,1)=@SubSectorCode )
	                                    set @max = (SELECT ISNULL(MAX(CONVERT(INT,SUBSTRING(InvoiceNo,11,5))),0)+1 InvoiceNo FROM transactions.recInvoice where SUBSTRING(InvoiceNo,5,2) = MONTH(GETDATE()) AND SUBSTRING(InvoiceNo,1,1)=@SectorCode AND SUBSTRING(InvoiceNo,3,1)=@SubSectorCode);
                                    else
	                                    set @max = 1;

                                    select @max;	
                                end ";

                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@SectorCode", SqlDbType.VarChar).Value = sectorCode;
                    cmd.Parameters.Add("@SubSectorCode", SqlDbType.VarChar).Value = subSectorCode;
                    cmd.Transaction = txn;
                    var objId = cmd.ExecuteScalar();
                    int invID = int.Parse(objId.ToString());
                    cmd.Dispose();

                    corporateAction.InvoiceNo = sectorCode + "." + subSectorCode + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + invID.ToString().PadLeft(5, '0');
                    invoice.InvoiceNo = corporateAction.InvoiceNo;
                    invoiceDetail.InvoiceNo = corporateAction.InvoiceNo;
                    billing.InvoiceNo = corporateAction.InvoiceNo;

//                    cmd = conn.CreateCommand();
//                    cmd.CommandText = @"
//                                begin
//                                    declare @max int = 1;
//
//                                    if Exists(SELECT ISNULL(MAX(CONVERT(INT,SUBSTRING(BillingNo,9,5))),0) BillingNo FROM transactions.recBilling where LEN(BillingNo)=13 and SUBSTRING(BillingNo,3,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,1)=4)
//	                                    set @max = (SELECT ISNULL(MAX(CONVERT(INT,SUBSTRING(BillingNo,9,5))),0)+1 BillingNo FROM transactions.recBilling where LEN(BillingNo)=13 and SUBSTRING(BillingNo,3,2) = MONTH(GETDATE()) AND SUBSTRING(BillingNo,1,1)=4);
//                                    else
//	                                    set @max = 1;
//
//                                    select @max;	
//                                end ";

//                    cmd.CommandType = CommandType.Text;
//                    cmd.Transaction = txn;
//                    objId = cmd.ExecuteScalar();
//                    invID = int.Parse(objId.ToString());
//                    cmd.Dispose();

//                    billing.BillingNo = "4." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + invID.ToString().PadLeft(5, '0');

                    #region Generate BillingNo dari SP untuk lebih Bayar

                    SqlCommand cmdGenerateBillingNo = conn.CreateCommand();
                    cmdGenerateBillingNo.CommandText = "[dbo].[SP_GenTransCode]";
                    cmdGenerateBillingNo.CommandType = CommandType.StoredProcedure;
                    cmdGenerateBillingNo.Parameters.Add("@kode", SqlDbType.VarChar).Value = "4";
                    SqlParameter prmResult = new SqlParameter();
                    prmResult.ParameterName = "@hasil";
                    prmResult.Direction = ParameterDirection.Output;
                    prmResult.SqlDbType = SqlDbType.VarChar;
                    prmResult.Size = 100;
                    cmdGenerateBillingNo.Parameters.Add(prmResult);
                    cmdGenerateBillingNo.Transaction = txn;
                    cmdGenerateBillingNo.ExecuteNonQuery();
                    cmdGenerateBillingNo.Dispose();

                    var genBillingNo = prmResult.Value;

                    #endregion

                    billing.BillingNo = genBillingNo.ToString().Trim();
                    corporateAction.BillingNo = billing.BillingNo;
                    invoiceDetail.BillingNo = billing.BillingNo;

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recBilling(BillingNo, DebtorSectorID, TransTypeID, TariffID, Value, InvoiceNo, InvoiceDetailID, Balance, PrevBillingNo, BillingDate, DueDate, IsCancel, ForPayment, SaikoNo, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@BillingNo, @DebtorSectorID, @TransTypeID, @TariffID, @Value, @InvoiceNo, @InvoiceDetailID, @Balance, @PrevBillingNo, @BillingDate, @DueDate, @IsCancel, @ForPayment, @SaikoNo, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = billing.DebtorSectorID;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = billing.TransTypeID;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)billing.TariffID ?? DBNull.Value;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = billing.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)billing.InvoiceNo ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceDetailID", SqlDbType.SmallInt).Value = (object)billing.InvoiceDetailID ?? DBNull.Value;
                    cmd.Parameters.Add("@Balance", SqlDbType.Decimal).Value = (object)billing.Balance ?? DBNull.Value;
                    cmd.Parameters.Add("@PrevBillingNo", SqlDbType.VarChar).Value = (object)billing.PrevBillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingDate", SqlDbType.Date).Value = (object)billing.BillingDate ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDate", SqlDbType.Date).Value = (object)billing.DueDate ?? DBNull.Value;
                    cmd.Parameters.Add("@IsCancel", SqlDbType.Bit).Value = (object)billing.IsCancel ?? DBNull.Value;
                    cmd.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)billing.ForPayment ?? DBNull.Value;
                    cmd.Parameters.Add("@SaikoNo", SqlDbType.VarChar).Value = (object)billing.SaikoNo ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)billing.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)billing.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Billing
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recCorporateAction(CorporateActionTypeID, DebtorSectorID, CorporateSector, DebtorID, ImpositionValue, ImpositionBaseID, CorporateActionDate, CorporateName, NPWP, Address1, Address2, Address3, RegionID, CityID, ZipCode, Email, Phone, Fax, Website, InvoiceNo, InvoiceDate, PaymentNo, PaymentDate, CreateDate, CreateBy, UpdateDate, UpdateBy, BillingNo) VALUES(@CorporateActionTypeID, @DebtorSectorID, @CorporateSector, @DebtorID, @ImpositionValue, @ImpositionBaseID, @CorporateActionDate, @CorporateName, @NPWP, @Address1, @Address2, @Address3, @RegionID, @CityID, @ZipCode, @Email, @Phone, @Fax, @Website, @InvoiceNo, @InvoiceDate, @PaymentNo, @PaymentDate, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @BillingNo)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@CorporateActionTypeID", SqlDbType.SmallInt).Value = (object)corporateAction.CorporateActionTypeID ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = (object)corporateAction.DebtorSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@CorporateSector", SqlDbType.VarChar).Value = (object)corporateAction.CorporateSector ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = (object)corporateAction.DebtorID ?? DBNull.Value;
                    cmd.Parameters.Add("@ImpositionValue", SqlDbType.Decimal).Value = (object)corporateAction.ImpositionValue ?? DBNull.Value;
                    cmd.Parameters.Add("@ImpositionBaseID", SqlDbType.SmallInt).Value = (object)corporateAction.ImpositionBaseID ?? DBNull.Value;
                    cmd.Parameters.Add("@CorporateActionDate", SqlDbType.Date).Value = (object)corporateAction.CorporateActionDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CorporateName", SqlDbType.VarChar).Value = (object)corporateAction.CorporateName ?? DBNull.Value;
                    cmd.Parameters.Add("@NPWP", SqlDbType.VarChar).Value = (object)corporateAction.NPWP ?? DBNull.Value;
                    cmd.Parameters.Add("@Address1", SqlDbType.VarChar).Value = (object)corporateAction.Address1 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address2", SqlDbType.VarChar).Value = (object)corporateAction.Address2 ?? DBNull.Value;
                    cmd.Parameters.Add("@Address3", SqlDbType.VarChar).Value = (object)corporateAction.Address3 ?? DBNull.Value;
                    cmd.Parameters.Add("@RegionID", SqlDbType.Int).Value = (object)corporateAction.RegionID ?? DBNull.Value;
                    cmd.Parameters.Add("@CityID", SqlDbType.Int).Value = (object)corporateAction.CityID ?? DBNull.Value;
                    cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar).Value = (object)corporateAction.ZipCode ?? DBNull.Value;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = (object)corporateAction.Email ?? DBNull.Value;
                    cmd.Parameters.Add("@Phone", SqlDbType.VarChar).Value = (object)corporateAction.Phone ?? DBNull.Value;
                    cmd.Parameters.Add("@Fax", SqlDbType.VarChar).Value = (object)corporateAction.Fax ?? DBNull.Value;
                    cmd.Parameters.Add("@Website", SqlDbType.VarChar).Value = (object)corporateAction.Website ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = (object)corporateAction.InvoiceNo ?? DBNull.Value;
                    cmd.Parameters.Add("@InvoiceDate", SqlDbType.Date).Value = (object)corporateAction.InvoiceDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)corporateAction.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.Date).Value = (object)corporateAction.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreateDate", SqlDbType.Date).Value = (object)corporateAction.CreateDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)corporateAction.CreateBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdateDate", SqlDbType.Date).Value = (object)corporateAction.UpdateDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)corporateAction.UpdateBy ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)corporateAction.BillingNo ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save CorporateAction
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recInvoice(InvoiceNo, InvoiceDate, InvoiceValue, BankID, BeSpelled, PaymentNo, PaymentDate, PaymentValue, IsHapusBuku, IsHapusTagih, DebtorSectorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @InvoiceDate, @InvoiceValue, @BankID, @BeSpelled, @PaymentNo, @PaymentDate, @PaymentValue, @IsHapusBuku, @IsHapusTagih, @DebtorSectorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    cmd.CommandType = CommandType.Text;

                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoice.InvoiceNo;
                    cmd.Parameters.Add("@InvoiceDate", SqlDbType.DateTime).Value = invoice.InvoiceDate;
                    cmd.Parameters.Add("@InvoiceValue", SqlDbType.Decimal).Value = (object)invoice.InvoiceValue ?? DBNull.Value;
                    cmd.Parameters.Add("@BankID", SqlDbType.SmallInt).Value = invoice.BankID;
                    cmd.Parameters.Add("@BeSpelled", SqlDbType.VarChar).Value = (object)invoice.BeSpelled ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentNo", SqlDbType.VarChar).Value = (object)invoice.PaymentNo ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentDate", SqlDbType.DateTime).Value = (object)invoice.PaymentDate ?? DBNull.Value;
                    cmd.Parameters.Add("@PaymentValue", SqlDbType.Decimal).Value = (object)invoice.PaymentValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHapusBuku", SqlDbType.Bit).Value = (object)invoice.IsHapusBuku ?? DBNull.Value;
                    cmd.Parameters.Add("@IsHapusTagih", SqlDbType.Bit).Value = (object)invoice.IsHapusTagih ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = invoice.DebtorSectorID;

                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoice.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoice.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoice.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoice.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Invoice
                    cmd.Dispose();

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO transactions.recInvoiceDetail(InvoiceNo, Value, TransTypeID, TariffID, BillingNo, DueDate, ForPayment, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy) VALUES(@InvoiceNo, @Value, @TransTypeID, @TariffID, @BillingNo, @DueDate, @ForPayment, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy)";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = invoiceDetail.InvoiceNo;
                    cmd.Parameters.Add("@Value", SqlDbType.Decimal).Value = invoiceDetail.Value;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = invoiceDetail.TransTypeID;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)invoiceDetail.TariffID ?? DBNull.Value;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = (object)invoiceDetail.BillingNo ?? DBNull.Value;
                    cmd.Parameters.Add("@DueDate", SqlDbType.DateTime).Value = (object)invoiceDetail.DueDate ?? DBNull.Value;
                    cmd.Parameters.Add("@ForPayment", SqlDbType.VarChar).Value = (object)invoiceDetail.ForPayment ?? DBNull.Value;

                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)invoiceDetail.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)invoiceDetail.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)invoiceDetail.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Invoice Detail
                    cmd.Dispose();

                    string strGetInvoiceDetailId = " SELECT MAX(InvoiceDetailID) InvoiceDetailID FROM transactions.recInvoiceDetail WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetInvoiceDetailId = new SqlCommand(strGetInvoiceDetailId, conn);
                    cmdGetInvoiceDetailId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = invoiceDetail.CreatedBy;

                    cmdGetInvoiceDetailId.Transaction = txn;
                    var objInvoiceDetailId = cmdGetInvoiceDetailId.ExecuteScalar(); // Get Invoice Detail ID
                    cmdGetInvoiceDetailId.Dispose();

                    int invoiceDetailID = objInvoiceDetailId == DBNull.Value ? 0 : Convert.ToInt32(objInvoiceDetailId.ToString()); // Get InvoiceDetailId

                    billing.InvoiceDetailID = (short)invoiceDetailID;

                    cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE transactions.recBilling SET InvoiceDetailID = @InvoiceDetailID, UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE BillingNo = @BillingNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@BillingNo", SqlDbType.VarChar).Value = billing.BillingNo;
                    cmd.Parameters.Add("@InvoiceDetailID", SqlDbType.Int).Value = billing.InvoiceDetailID;

                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)billing.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)billing.UpdatedBy ?? DBNull.Value;

                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Update Billing -> InvoiceDetailID
                    cmd.Dispose();

                    result = true;
                    txn.Commit();
                }

                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }
            }
            return result;
        }

       public recCorporateAction GetByInvoiceNo(string InvoiceNo)
        {
            recCorporateAction t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT CorporateActionID, CorporateActionTypeID, DebtorSectorID, DebtorID, ImpositionValue, ImpositionBaseID, CorporateActionDate, CorporateName, CorporateSector, NPWP, Address1, Address2, Address3, RegionID, CityID, ZipCode, Email, Phone, Fax, Website, InvoiceNo, InvoiceDate, PaymentNo, PaymentDate, CreateDate, CreateBy, UpdateDate, UpdateBy, BillingNo FROM transactions.recCorporateAction WHERE InvoiceNo = @InvoiceNo";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@InvoiceNo", SqlDbType.VarChar).Value = InvoiceNo;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recCorporateAction();
                                t.CorporateActionID = dr[recCorporateAction.Fields.CorporateActionID] == DBNull.Value ? 0 : int.Parse(dr[recCorporateAction.Fields.CorporateActionID].ToString());
                                t.CorporateActionTypeID = dr[recCorporateAction.Fields.CorporateActionTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recCorporateAction.Fields.CorporateActionTypeID].ToString());
                                t.DebtorSectorID = dr[recCorporateAction.Fields.DebtorSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recCorporateAction.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recCorporateAction.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recCorporateAction.Fields.DebtorID].ToString());
                                t.ImpositionValue = dr[recCorporateAction.Fields.ImpositionValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recCorporateAction.Fields.ImpositionValue].ToString());
                                t.ImpositionBaseID = dr[recCorporateAction.Fields.ImpositionBaseID] == DBNull.Value ? (short?)null : short.Parse(dr[recCorporateAction.Fields.ImpositionBaseID].ToString());
                                t.CorporateActionDate = dr[recCorporateAction.Fields.CorporateActionDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recCorporateAction.Fields.CorporateActionDate].ToString());
                                t.CorporateName = dr[recCorporateAction.Fields.CorporateName] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.CorporateName].ToString();
                                t.CorporateSector = dr[recCorporateAction.Fields.CorporateSector] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.CorporateSector].ToString();
                                t.NPWP = dr[recCorporateAction.Fields.NPWP] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.NPWP].ToString();
                                t.Address1 = dr[recCorporateAction.Fields.Address1] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Address1].ToString();
                                t.Address2 = dr[recCorporateAction.Fields.Address2] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Address2].ToString();
                                t.Address3 = dr[recCorporateAction.Fields.Address3] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Address3].ToString();
                                t.RegionID = dr[recCorporateAction.Fields.RegionID] == DBNull.Value ? (int?)null : int.Parse(dr[recCorporateAction.Fields.RegionID].ToString());
                                t.CityID = dr[recCorporateAction.Fields.CityID] == DBNull.Value ? (int?)null : int.Parse(dr[recCorporateAction.Fields.CityID].ToString());
                                t.ZipCode = dr[recCorporateAction.Fields.ZipCode] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.ZipCode].ToString();
                                t.Email = dr[recCorporateAction.Fields.Email] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Email].ToString();
                                t.Phone = dr[recCorporateAction.Fields.Phone] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Phone].ToString();
                                t.Fax = dr[recCorporateAction.Fields.Fax] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Fax].ToString();
                                t.Website = dr[recCorporateAction.Fields.Website] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.Website].ToString();
                                t.InvoiceNo = dr[recCorporateAction.Fields.InvoiceNo] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.InvoiceNo].ToString();
                                t.InvoiceDate = dr[recCorporateAction.Fields.InvoiceDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recCorporateAction.Fields.InvoiceDate].ToString());
                                t.PaymentNo = dr[recCorporateAction.Fields.PaymentNo] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.PaymentNo].ToString();
                                t.PaymentDate = dr[recCorporateAction.Fields.PaymentDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recCorporateAction.Fields.PaymentDate].ToString());
                                t.CreateDate = dr[recCorporateAction.Fields.CreateDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recCorporateAction.Fields.CreateDate].ToString());
                                t.CreateBy = dr[recCorporateAction.Fields.CreateBy] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.CreateBy].ToString();
                                t.UpdateDate = dr[recCorporateAction.Fields.UpdateDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recCorporateAction.Fields.UpdateDate].ToString());
                                t.UpdateBy = dr[recCorporateAction.Fields.UpdateBy] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.UpdateBy].ToString();
                                t.BillingNo = dr[recCorporateAction.Fields.BillingNo] == DBNull.Value ? string.Empty : dr[recCorporateAction.Fields.BillingNo].ToString();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }
    }
}